package cn.fjssj.demo02.service.impl;

import cn.fjssj.demo02.base.BaseImportExcelVO;
import cn.fjssj.demo02.base.CountVO;
import cn.fjssj.demo02.config.TaskThreadPool;
import cn.fjssj.demo02.domain.DutyPanda;
import cn.fjssj.demo02.domain.ExcelTaskEntity;
import cn.fjssj.demo02.domain.PandaEntity;
import cn.fjssj.demo02.enums.*;
import cn.fjssj.demo02.exception.BusinessException;
import cn.fjssj.demo02.listener.CommonListener;
import cn.fjssj.demo02.mapper.PandaMapper;
import cn.fjssj.demo02.model.bo.PandaStatisticsBO;
import cn.fjssj.demo02.model.dto.PandaQueryDTO;
import cn.fjssj.demo02.model.dto.PandaStatisticsDTO;
import cn.fjssj.demo02.model.excel.PandaReadErrorModel;
import cn.fjssj.demo02.model.excel.PandaReadModel;
import cn.fjssj.demo02.model.vo.*;
import cn.fjssj.demo02.service.ExcelTaskService;
import cn.fjssj.demo02.service.PandaService;
import cn.fjssj.demo02.util.ExcelUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.web.client.RestTemplateBuilder;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.invoke.VarHandle;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

@Service
@Slf4j
public class PandaServiceImpl extends ServiceImpl<PandaMapper, PandaEntity> implements PandaService {
    @Autowired
    private ExcelTaskService excelTaskService;
    @Autowired
    private RestTemplateBuilder restTemplateBuilder;

    /**
     * 基于excel文件导入熊猫数据并落库存储
     *
     * @param file MultipartFile
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void importExcelV1(MultipartFile file) {
        // 创建通用监听器来解析excel文件
        CommonListener<PandaReadModel> listener = new CommonListener<>(PandaReadModel.class);
        try {
            EasyExcelFactory.read(file.getInputStream(), PandaReadModel.class, listener).sheet().doRead();
        } catch (IOException e) {
            log.error("导入熊猫数据出错：{}: {}", e, e.getMessage());
            throw new BusinessException(ResponseCode.ANALYSIS_EXCEL_ERROR, "网络繁忙，请稍后重试！");
        }

        // 对读取到的数据进行批量保存
        List<PandaReadModel> excelData = listener.getData();
        if (excelData.isEmpty()) {
            throw new BusinessException(ResponseCode.ANALYSIS_EXCEL_ERROR, "请检查您上传的excel文件是否为空！");
        }
        batchSaveExcelData(excelData);
    }

    /**
     * 批量保存导入的excel数据
     *
     * @param excelData List<PandaReadModel>
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void batchSaveExcelData(List<PandaReadModel> excelData) {
        List<PandaEntity> pandas = excelData.stream().map(model -> {
            PandaEntity panda = new PandaEntity();
            BeanUtils.copyProperties(model, panda, "sex");
            panda.setSex(Sex.codeOfValue(model.getSex()));
            panda.setCreateTime(new Date());
            return panda;
        }).collect(Collectors.toList());
        saveBatch(pandas);
    }

    /**
     * 根据搜索条件导出excel数据
     *
     * @param queryDTO PandaQueryDTO
     * @param response HttpServletResponse
     */
    @Override
    public void exportExcelByCondition(PandaQueryDTO queryDTO, HttpServletResponse response) {
        List<PandaExportVO> pandas = this.baseMapper.selectPandas(queryDTO);
        String fileName = "熊猫基本信息集合-" + System.currentTimeMillis();
        try {
            ExcelUtil.exportExcel(PandaExportVO.class, pandas, fileName, ExcelTypeEnum.XLSX, response);
        } catch (IOException e) {
            log.error("熊猫数据导出失败，{}：{}", e, e.getMessage());
            throw new BusinessException("熊猫基本信息导出失败，请稍后再试！");
        }
    }

    @Override
    public BaseImportExcelVO<String> importExcelV2(MultipartFile file) {
        CommonListener<PandaReadModel> listener = new CommonListener<>(PandaReadModel.class);
        try {
            EasyExcelFactory.read(file.getInputStream(), PandaReadModel.class, listener).sheet().doRead();
        } catch (IOException e) {
            log.error("导入熊猫数据出错：{}: {}", e, e.getMessage());
            throw new BusinessException(ResponseCode.ANALYSIS_EXCEL_ERROR, "网络繁忙，请稍后重试！");
        }

        List<PandaReadModel> excelData = listener.getData();
        if (excelData.size() == 0) {
            throw new BusinessException(ResponseCode.ANALYSIS_EXCEL_ERROR, "请检查您上传的excel文件是否为空！");
        }

        // 校验excel数据，如果校验未通过直接阻断执行，将错误信息返回给调用方
        BaseImportExcelVO<String> result = validateExcelData(excelData);
        if (result.getErrorFlag()) {
            return result;
        }

        // 对校验通过的数据进行批量落库
        batchSaveExcelData(excelData);
        return result;
    }

    /*
     * 校验导入的excel数据
     * */
    private BaseImportExcelVO<String> validateExcelData(List<PandaReadModel> excelData) {
        BaseImportExcelVO<String> result = new BaseImportExcelVO<>();
        boolean errorFlag = false;
        List<PandaReadErrorModel> validatePandas = new ArrayList<>();
        String birthdayErrorMsg = "生日不能为空;", uniCodeErrorMsg = "唯一编码重复;";

        Map<String, Integer> countMap = null;
        // 根据唯一编码查询库内的熊猫数
        try {
            List<String> uniCodes = excelData.stream().map(PandaReadModel::getUniqueCode).collect(Collectors.toList());
            List<CountVO<String, Integer>> counts = baseMapper.selectCountByUniCodes(uniCodes);
            countMap = counts.stream().collect(Collectors.toMap(CountVO::getKey, CountVO::getValue));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        // 循环对excel所有数据行进行校验
        for (PandaReadModel excelRow : excelData) {
            String errorMsg = "";
            PandaReadErrorModel errorModel = new PandaReadErrorModel();
            BeanUtils.copyProperties(excelRow, errorModel);
            // 如果库里对应的唯一编码能查到熊猫，说明UniqueCode重复
            if (countMap.containsKey(excelRow.getUniqueCode())) {
                errorMsg += uniCodeErrorMsg;
                errorFlag = true;
            }
            // 如果导入的生日字段为空，说明对应的excel行没填写出生日期
            if (null == excelRow.getBirthday()) {
                errorMsg += birthdayErrorMsg;
                errorFlag = true;
            }
            errorModel.setErrorMsg(errorMsg);
            validatePandas.add(errorModel);
        }

        // 如果存在校验未通过的记录，则导出校验出错的数据为excel文件
        if (errorFlag) {
            String url, fileName = "熊猫信息导入-校验出错文件-" + System.currentTimeMillis();
            try {
                url = ExcelUtil.exportExcelToOSS(PandaReadErrorModel.class, validatePandas, fileName, ExcelTypeEnum.XLSX);
//                ExcelUtil.exportExcel(PandaReadErrorModel.class, validatePandas, fileName, ExcelTypeEnum.XLSX, response);
            } catch (Exception e) {
                log.error("生成熊猫导入校验出错文件失败：{}: {}", e, e.getMessage());
                throw new BusinessException(ResponseCode.ANALYSIS_EXCEL_ERROR, "网络繁忙，请稍后重试！");
            }
            result.setResult(url);
            result.setCheckMsg("文件校验未通过！");
        }
        result.setErrorFlag(errorFlag);
        return result;
    }

    /**
     * 0：代表按性别统计数据，使用SexStatisticsExportVO模型类；
     * 1：代表按等级统计数据，使用LevelStatisticsExportVO模型类。
     *
     * @param statisticsDTO
     * @param response
     */
    @Override
    public void exportStatisticsData(PandaStatisticsDTO statisticsDTO, HttpServletResponse response) {
        List<PandaStatisticsBO> pandaStatisticsBOs = baseMapper.selectPandaStatistics(statisticsDTO);

        List<PandaStatisticsExportVO> exportData;
        Class<?> clazz;
        // 如果是按性别分组统计，则使用性别的数据模型类
        if (0 == statisticsDTO.getStatisticsType()) {
            clazz = SexStatisticsExportVO.class;
            exportData = pandaStatisticsBOs.stream().map(bo -> {
                SexStatisticsExportVO sexVO = new SexStatisticsExportVO();
                BeanUtils.copyProperties(bo, sexVO);
                return sexVO;
            }).collect(Collectors.toList());
        }
        // 如果是按等级分组统计，则使用等级的数据模型类
        else if (1 == statisticsDTO.getStatisticsType()) {
            clazz = LevelStatisticsExportVO.class;
            exportData = pandaStatisticsBOs.stream().map(bo -> {
                LevelStatisticsExportVO levelVO = new LevelStatisticsExportVO();
                BeanUtils.copyProperties(bo, levelVO);
                return levelVO;
            }).collect(Collectors.toList());
        } else {
            throw new BusinessException("暂不支持这种统计方式哦~");
        }

        // 导出对应的excel数据
        String fileName = "熊猫统计数据-" + System.currentTimeMillis();
        try {
            ExcelUtil.exportExcel(clazz, exportData, fileName, ExcelTypeEnum.XLSX, response);
        } catch (IOException e) {
            log.error("熊猫统计数据导出失败，{}：{}", e, e.getMessage());
            throw new BusinessException("统计数据导出失败，请稍后再试！");
        }
    }

    @Override
    public void exportMultiLineHeadExcel(HttpServletResponse response) {
        List<MultiLineHeadExportVO> pandas = baseMapper.selectAllPandas();
        String fileName = "多行表头熊猫数据-" + System.currentTimeMillis();
        try {
            ExcelUtil.exportExcel(MultiLineHeadExportVO.class, pandas, fileName, ExcelTypeEnum.XLSX, response);
        } catch (IOException e) {
            log.error("多行表头熊猫数据导出出错，{}：{}", e, e.getMessage());
            throw new BusinessException("数据导出失败，请稍后再试！");
        }
    }

    @Override
    public void fillExcelV1(PandaEntity panda, HttpServletResponse response) {
        // 创建要填充的数据对象
        // PandaEntity panda = new PandaEntity();
        // panda.setName("竹子");
        // panda.setNickname("小竹");
        // panda.setUniqueCode("P888888");
        // panda.setAddress("地球村888号");
        // panda.setHeight(new BigDecimal("188.88"));
        // panda.setMotto("今天的事能拖就拖，明天的事明天再说！");

        // 声明模板的位置、生成的excel文件名，开始正式填充数据
        String templatePath = "excel/simple_template.xlsx";
        String fileName1 = "excel/panda_entity_info.xlsx";
        EasyExcelFactory.write(fileName1).withTemplate(templatePath).sheet().doFill(panda);
    }

    @Override
    public void fillExcelV2(HttpServletResponse response) {
        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("name", "竹子");
        dataMap.put("nickname", "小竹");
        dataMap.put("uniqueCode", "P888888");
        dataMap.put("address", "地球村888号");
        dataMap.put("height", new BigDecimal("188.88"));
        dataMap.put("motto", "今天的事能拖就拖，明天的事明天再说！");
        String templatePath = "excel/simple_template.xlsx";
        String fileName2 = "excel/panda_map_info.xlsx";
        EasyExcelFactory.write(fileName2).withTemplate(templatePath).sheet().doFill(dataMap);
    }

    @Override
    public void fillExcelV3(Integer num, String name, HttpServletResponse response) {
        List<PandaEntity> pandas = new ArrayList<>();
        for (int i = 1001; i <= (num + 1001); i++) {
            PandaEntity panda = new PandaEntity();
            panda.setName(name + i + "号");
            panda.setUniqueCode("P" + i);
            panda.setBirthday(new Date());
            panda.setAddress("地址在" + name + "-" + i + "号");
            pandas.add(panda);
        }
        String fileName = "pandas_v123456.xlsx";
        String templatePath = "excel/list_template.xlsx";
        // EasyExcelFactory.write(fileName).withTemplate(templatePath).sheet().doFill(pandas);

        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName);

        try {
            ExcelWriterBuilder writeWork = EasyExcelFactory.write(response.getOutputStream());
            writeWork.withTemplate(templatePath).excelType(ExcelTypeEnum.XLSX).sheet().doFill(pandas);
        } catch (Exception e) {
            throw new BusinessException("到处报错：" + e.getMessage());
        }
    }

    /**
     * 分批填充
     * 注意看代码，这里是手动先创建了写入对象，然后再在循环内部去多次触发填充动作，这种适用于数据量较大的填充场景（效果和前面的一样，就不贴图了）。
     *
     * @param num
     * @param name
     * @param response
     */
    @Override
    public void fillExcelV4(Integer num, String name, HttpServletResponse response) {
        String fileName = "pandas_v2.xlsx";
        String templatePath = "excel/list_template.xlsx";

        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName);

        try {
            // 先创建一个Excel工作簿写对象、Sheet写对象
            ExcelWriter excelWriter = EasyExcelFactory.write(response.getOutputStream()).withTemplate(templatePath).excelType(ExcelTypeEnum.XLSX).build();
            WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();
            // 模拟多次填充（填充五次，每次填充一条数据）
            for (int i = 3001; i <= (3001 + num); i++) {
                List<PandaEntity> pandas = new ArrayList<>();
                PandaEntity panda = new PandaEntity();
                panda.setName(name + i + "号");
                panda.setUniqueCode("P" + i);
                panda.setBirthday(new Date());
                panda.setAddress("地址在" + name + "-" + i + "号");
                pandas.add(panda);
                // 开始触发数据填充
                excelWriter.fill(pandas, writeSheet);
            }
            // 手动创建的写对象记得手动关
            excelWriter.finish();
        } catch (Exception e) {
            throw new BusinessException("到处报错：" + e.getMessage());
        }
    }

    @Override
    public void mixedFill(Integer num, String name, HttpServletResponse response) {
        // 导出的位置、模板的位置
        String fileName = "mixed_data-" + System.currentTimeMillis() + ".xlsx";
        String templatePath = "excel/mixed_template.xlsx";

        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName);

        try {
            // 提前创建excel写对象
            ExcelWriter excelWriter = EasyExcelFactory.write(response.getOutputStream()).
                    withTemplate(templatePath).excelType(ExcelTypeEnum.XLSX).build();
            WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();
            /*
            重点看这里出现了一个FillConfig类：
                这个类从名字上就能看出作用，主要就是用于开启一些填充数据时的配置项，
                比如上述案例中的forceNewRow(Boolean.TRUE)，代表开启自动创建新行，为啥要这么设置呢？
            来看官方的说法：
                forceNewRow代表在填充列表数据时，不管列表下面有没有空行，都会创建一行，然后下面的数据往后移动。
                默认是false，会直接使用下一行，如果没有则创建。
            * 啥意思呢？有点迷糊是不？其实简单的说，就是当模板需要填充列表数据，并且list占位符不在最后一行，就必须将forceNewRow设置为true。
            结合前面定义的模板来看，列表占位符定义在第四行，可是在第五行还有身高统计的占位符，这说明在列表填充完成后，
            下面还有数据需要填充，如果不开启自动创建新行，就会导致“身高统计行”被覆盖掉。
            * */
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();

            // 初始化熊猫列表数据
            List<PandaEntity> pandas = new ArrayList<>();
            BigDecimal total = BigDecimal.ZERO;
            for (int i = 2001; i <= (2001 + num); i++) {
                PandaEntity panda = new PandaEntity();
                panda.setName(name + i + "号");
                panda.setUniqueCode("P" + i);
                // 对熊猫身高求和
                BigDecimal height = new BigDecimal(i * 15.5);
                total = total.add(height);
                panda.setHeight(height);
                panda.setAddress(name + i + "号");
                pandas.add(panda);
            }

            // 组装普通的数据（这里用了Map，不嫌麻烦也可以定义单独的类）
            BigDecimal avg = total.divide(new BigDecimal(pandas.size()), RoundingMode.HALF_UP);
            Map<String, Object> dataMap = new HashMap<>();
            dataMap.put("username", "竹子爱熊猫");
            dataMap.put("date", new Date());
            dataMap.put("totalHeight", total);
            dataMap.put("avgHeight", avg);

            // 写入熊猫集合数据、普通数据
            excelWriter.fill(pandas, fillConfig, writeSheet);
            excelWriter.fill(dataMap, writeSheet);
            excelWriter.finish();
        } catch (Exception e) {
            throw new RuntimeException("混合导出失败：" + e.getMessage());
        }
    }

    /**
     * 这里横向填充列表数据，又用到了FillConfig这个类，这个类提供的可选项总共只有四个：
     * direction：填充的方向，默认为VERTICAL垂直方向，HORIZONTAL水平方向；
     * forceNewRow：十分自动创建新行填充数据，默认关闭，某些场景下会造成数据覆盖；
     * autoStyle：是否开启样式自动适配，开启后会根据模板里的样式填充数据，默认开启；
     * hasInit：是否允许默认初始化，关闭后每个选项没有默认值，必须手动指定才生效。
     * <p>
     * 因为咱们需要横向填充数据，所以得将direction选项设置为HORIZONTAL水平方向
     *
     * @param num
     * @param name
     * @param response
     */
    @Override
    public void transverseListFill(Integer num, String name, HttpServletResponse response) {
        // String fileName = "transverse_list_data-"+System.currentTimeMillis()+".xlsx";
        String fileName = "transverse_list_data-" + name + "-" + System.currentTimeMillis();
        fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8).replaceAll("\\+", "%20") + ExcelTypeEnum.XLSX.getValue();
        String templatePath = "excel/transverse_list_template.xlsx";

        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName);

        try {
            // 构建需要填充的数据
            List<DutyPanda> dutyPandas = new ArrayList<>();
            for (int i = 1; i <= num; i++) {
                DutyPanda dutyPanda = new DutyPanda();
                dutyPanda.setWeek("周" + i);
                dutyPanda.setName("竹子" + i + "号");
                dutyPandas.add(dutyPanda);
            }
            Map<String, Object> dataMap = new HashMap<>();
            dataMap.put("date", new Date());

            // 提前创建excel写对象
            ExcelWriter excelWriter = EasyExcelFactory.write(response.getOutputStream()).withTemplate(templatePath)
                    .excelType(ExcelTypeEnum.XLSX).build();
            WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();
            // 设置填充模式为水平填充
            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
            excelWriter.fill(dutyPandas, fillConfig, writeSheet);
            excelWriter.fill(dataMap, writeSheet);
            excelWriter.finish();
        } catch (Exception e) {
            throw new RuntimeException("水平填充报错：" + e.getMessage());
        }
    }

    /**
     * 这个模板中，需要填充两个列表数据，所以定义占位符时用list1、list2两个变量名来区分，那填充数据时，如何指定Java集合数据与占位符的对应关系呢？来看代码：
     * 代码中大多数都是熟悉的类，只不过出现了一个FillWrapper类，这个类就是用来指定Java集合，和模板中列表占位符的映射关系。
     * 述代码中，dutyPandas对应模板里的list1，pandas对应list2，来看最终效果：
     *
     * @param num
     * @param name
     * @param response
     */
    @Override
    public void manyListFill(Integer num, String name, HttpServletResponse response) {
        String fileName = "many_list_data-" + name + "-" + System.currentTimeMillis();
        fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8).replaceAll("\\+", "%20") + ExcelTypeEnum.XLSX.getValue();

        String templatePath = "excel/many_list_template.xlsx";

        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName);

        // 构建需要填充的数据
        List<DutyPanda> dutyPandas = new ArrayList<>();
        List<PandaEntity> pandas = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            String fullName = name + i + "号";
            if (i <= 5) {
                DutyPanda dutyPanda = new DutyPanda();
                dutyPanda.setWeek("周" + i);
                dutyPanda.setName(fullName);
                dutyPandas.add(dutyPanda);
            } else {
                PandaEntity panda = new PandaEntity();
                panda.setName(fullName);
                panda.setUniqueCode("P" + i);
                panda.setAddress("地球村" + i + "号");
                pandas.add(panda);
            }
        }
        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("date", new Date());
        dataMap.put("username", name + "爱熊猫(" + num + ")");
        dataMap.put("remark", "最终解释权归分配人所有！");

        try (
                ServletOutputStream outputStream = response.getOutputStream();
                ExcelWriter excelWriter = EasyExcelFactory.write(outputStream)
                        .withTemplate(templatePath).excelType(ExcelTypeEnum.XLSX).build()) {
            // 提前创建excel写对象
            WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();
            // 设置填充模式为水平填充
            FillConfig config1 = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
            // 设置自动创建新行填充
            FillConfig config2 = FillConfig.builder().forceNewRow(true).build();

            excelWriter.fill(new FillWrapper("list1", dutyPandas), config1, writeSheet);
            excelWriter.fill(new FillWrapper("list2", pandas), config2, writeSheet);
            excelWriter.fill(dataMap, writeSheet);
            excelWriter.finish();
        } catch (IOException e) {
            throw new RuntimeException("多列表数据填充场景报错：" + e.getMessage());
        }
    }

    /**
     * 导出熊猫统计数据（复杂报表）
     * <p>
     * ①从数据库里将整张熊猫表的数据查询出来；
     * ②提前初始化各统计项的变量，并循环熊猫集合统计数据；
     * ③在循环内部分别统计模板所需的性别、身高、新增等熊数；
     * ④调用封装的通用方法，并将列表数据、统计数据填充并返回。
     *
     * @param response
     */
    @Override
    public void exportPandaStatisticsData(HttpServletResponse response) {
        // 查询熊猫集合数据
        List<PandaEntity> pandas = baseMapper.selectAll();

        // 初始化各统计项变量
        String statisticsUser = "竹子爱熊猫";
        Date statisticsDate = new Date();
        int maleNumber = 0, femaleNumber = 0, unknownNumber = 0, totalNumber = 0,
                weekIncreaseNumber = 0, monthIncreaseNumber = 0, yearIncreaseNumber = 0;
        BigDecimal avgHeight, maxHeight = null, minHeight = null, sumHeight = BigDecimal.ZERO;

        Calendar today = Calendar.getInstance();
        int currentYear = today.get(Calendar.YEAR);
        int currentMonth = today.get(Calendar.MONTH) + 1;
        int currentWeek = today.get(Calendar.WEEK_OF_YEAR);

        for (PandaEntity panda : pandas) {
            // 统计各项性别数据
            Sex sex = Sex.ofCode(panda.getSex());
            switch (sex) {
                case MALE -> maleNumber++;
                case FEMALE -> femaleNumber++;
                default -> unknownNumber++;
            }

            // 统计各项身高数据
            BigDecimal height = panda.getHeight();
            sumHeight = sumHeight.add(height);
            if (null == maxHeight) {
                maxHeight = height;
            }
            if (null == minHeight) {
                minHeight = height;
            }

            if (minHeight.compareTo(height) < 0) {
                minHeight = height;
            }
            if (maxHeight.compareTo(height) > 0) {
                maxHeight = height;
            }

            // 统计本年、本月、本周新增人数
            Calendar createCal = Calendar.getInstance();
            createCal.setTime(panda.getCreateTime());
            int year = createCal.get(Calendar.YEAR);
            int month = createCal.get(Calendar.MONTH) + 1;
            int week = createCal.get(Calendar.WEEK_OF_YEAR);
            if (year == currentYear) {
                yearIncreaseNumber++;
                if (month == currentMonth) {
                    monthIncreaseNumber++;
                    if (week == currentWeek) {
                        weekIncreaseNumber++;
                    }
                }
            }
        }
        totalNumber = pandas.size();
        avgHeight = sumHeight.divide(new BigDecimal(totalNumber), RoundingMode.HALF_UP);

        Map<String, Object> dataMap = new HashMap<>(13);
        dataMap.put("statisticsUser", statisticsUser);
        dataMap.put("statisticsDate", statisticsDate);
        dataMap.put("maleNumber", maleNumber);
        dataMap.put("femaleNumber", femaleNumber);
        dataMap.put("unknownNumber", unknownNumber);
        dataMap.put("weekIncreaseNumber", weekIncreaseNumber);
        dataMap.put("monthIncreaseNumber", monthIncreaseNumber);
        dataMap.put("yearIncreaseNumber", yearIncreaseNumber);
        dataMap.put("avgHeight", avgHeight);
        dataMap.put("maxHeight", maxHeight);
        dataMap.put("minHeight", minHeight);
        dataMap.put("totalNumber", totalNumber);
        dataMap.put("illustrate", "以上数据来自熊猫表");

        String fileName = "熊猫数据统计报表";
        // 获取一个已设置基础参数的ExcelWriter对象
        try (ExcelWriter excelWriter = ExcelUtil.initExportFillWriter(fileName,
                ExcelTypeEnum.XLSX, ExcelTemplate.PANDA_STATISTICS, response)) {
            WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();
            // 基于模板填充数据
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(pandas, fillConfig, writeSheet);
            excelWriter.fill(dataMap, writeSheet);
            excelWriter.finish();
        } catch (IOException e) {
            throw new BusinessException("熊猫统计报表导出失败！");
        }
    }

    /*
    * 处理报表导出任务
    * 一眼看下来，逻辑稍微有点点复杂，下面先给出整体流程：
    ①当开始处理对应的报表任务时，会先将状态推进到”处理中“；
    ②查询当前任务需要导出的总条数，为零则直接推进到”失败”状态；
    ③这一步是前置变量的运算，得到了多个后续依赖的值：
        batchRows：每批处理的数量，这里写死为2000；
        batchNum：批次数，即总条数要分为多少批查询；
        concurrentRound：并发轮数，每轮并发处理3批；
    ④提前创建了excel写对象、文件名及游标，后续用于追加写入：
        注意：我没有OSS，这里直接选择存到了本地，大家可根据实际情况调整；
    ⑤遍历并发轮数，开始以每轮三批的速率，向并发线程池投递任务；
    ⑥异步线程阻塞等待三批数据返回，拿到后将数据写入到excel文件；
    ⑦不断重复⑤、⑥步骤，最终将所有目标数据查出并写入到excel文件。
    ⑧当数据导出完成后收尾，将任务推到“成功”状态，并回填可访问的链接。

    * 大家可以结合这个流程多看几遍代码，毕竟代码中用到了线程池、CountDownLatch、写时复制并发容器、原子类这些并发工具，接触较少的小伙伴看起来或许比较迷糊。
    * OK，现在挑些重点来聊一聊，主要是⑤、⑥这两步，在这里通过AtomicInteger定义了一个游标，主要是用于计算起始的ID，它会随着批次不断更新，
    * 而使用原子类型的原因，是为了保障并发线程更新游标时的线程安全。

    * 其次，遍历并发轮数时，每一轮都会向concurrentPool并发线程池投递三个查询任务，这意味着会出现三条线程去同时处理数据（实际业务中可以替换成带有清洗逻辑的方法），
    * 每次投递后也会更新游标确保数据不会重复。然后CountDownLatch来实现线程通信，每次初始化的信号量都是 并发线程的数量。
    * 每当一条线程获取到数据后，就会将数据添加进CopyOnWriteArrayList类型的data集合，并且会扣除一个信号量，而外面的异步线程（主线程）在投递完三个任务后，
    * 会调用await()方法等待唤醒，当三条并发线程将数据都查出来后，countDownLatch对应的信号量会变为0，这时异步线程就会被唤醒。
    * 唤醒异步线程后，它会将data追加写入到excel文件，写入完成后会清理data集合，以此来辅助GC更快的识别垃圾对象。
    * 处理完前面两两步后，又会开启下一轮并发处理，如此不断反复，直至所有数据导出完毕为止。
    * */
    private void handleExportTask2(Long taskId) {
        long startTime = System.currentTimeMillis();
        log.info("处理报表导出任务开始，编号：{}，时间戳：{}", taskId, startTime);
        // 开始执行时，先将状态推进到进行中
        excelTaskService.updateStatus(taskId, TaskHandleStatus.IN_PROGRESS);

        // 需要修改的报表对象
        ExcelTaskEntity editTask = new ExcelTaskEntity();
        editTask.setTaskId(taskId);

        // 查询导出的总行数，如果为0，说明没有数据要导出，直接将任务推进到失败状态
        int totalRows = baseMapper.selectTotalRows();
        if (totalRows == 0) {
            editTask.setHandleStatus(TaskHandleStatus.FAILED.getCode());
            editTask.setExceptionType("数据为空");
            editTask.setErrorMsg("对应导出任务没有数据可导出！");
            editTask.setUpdateTime(new Date());
            excelTaskService.updateById(editTask);
            return;
        }

        // 总数除以每批数量，并向上取整得到批次数
        int batchRows = 2000;
        int batchNum = totalRows / batchRows + (totalRows % batchRows == 0 ? 0 : 1);
        // 总批次数除以并发比例，并向上取整得到并发轮数
        int concurrentRound = batchNum / TaskThreadPool.concurrentRate
                + (batchNum % TaskThreadPool.concurrentRate == 0 ? 0 : 1);;

        log.info("本次报表导出任务-目标数据量：{}条，每批数量：{}，总批次数：{}，并发总轮数：{}", totalRows, batchRows, batchNum, concurrentRound);

        // 提前创建excel写入对象（这里可以替换成上传至文件服务器）
        String fileName = "百万级熊猫数据-" + startTime + ".csv";
        ExcelWriter excelWriter = EasyExcelFactory.write(fileName, Panda1mExportVO.class)
                .excelType(ExcelTypeEnum.CSV)
                .build();
        // CSV文件这行其实可以不需要，设置了也不会生效
        WriteSheet writeSheet = EasyExcelFactory.writerSheet(0, "百万熊猫数据").build();

        // 根据计算出的并发轮数开始并发读取表内数据处理
        AtomicInteger cursor = new AtomicInteger(0);
        ThreadPoolTaskExecutor concurrentPool = TaskThreadPool.getConcurrentThreadPool();
        for (int i = 1; i <= concurrentRound; i++) {
            CountDownLatch countDownLatch = new CountDownLatch(TaskThreadPool.concurrentRate);
            final CopyOnWriteArrayList<Panda1mExportVO> data = new CopyOnWriteArrayList<>();
            for (int j = 0; j < TaskThreadPool.concurrentRate; j++) {
                final int startId = cursor.get() * batchRows + 1;
//                    log.info("当前批次：{}，起始ID：{}，线程池可用线程数：{}", cursor.get(), startId, concurrentPool.getActiveCount());
                concurrentPool.submit(() -> {
                    List<Panda1mExportVO> pandas = baseMapper.selectPandaPage((long) startId, batchRows);
                    if (null != pandas && pandas.size() != 0) {
                        data.addAll(pandas);
                    }
                    countDownLatch.countDown();
                });
                cursor.incrementAndGet();
            }

            try {
                countDownLatch.await();
            } catch (InterruptedException e) {
                editTask.setHandleStatus(TaskHandleStatus.FAILED.getCode());
                editTask.setExceptionType("导出等待中断");
                editTask.setErrorMsg(e.getMessage());
                editTask.setUpdateTime(new Date());
                excelTaskService.updateById(editTask);
                return;
            }
            excelWriter.write(data, writeSheet);
            // 手动清理每一轮的集合数据，用于辅助GC
            data.clear();
        }

        log.info("处理报表导出任务结束，编号：{}，导出耗时（ms）：{}", taskId, System.currentTimeMillis() - startTime);
        // 完成写入后，主动关闭资源
        excelWriter.finish();

        // 如果执行到最后，说明excel导出成功，将状态推进到导出成功
        editTask.setHandleStatus(TaskHandleStatus.SUCCEED.getCode());
        editTask.setExcelUrl(fileName);
        editTask.setUpdateTime(new Date());
        excelTaskService.updateById(editTask);
    }

    /*
     * 处理报表导出任务
     * */
    private void handleExportTask(Long taskId) {
        long startTime = System.currentTimeMillis();
        log.info("处理报表导出任务开始，编号：{}，时间戳：{}", taskId, startTime);
        // 开始执行时，先将状态推进到进行中
        excelTaskService.updateStatus(taskId, TaskHandleStatus.IN_PROGRESS);
        // 查询导出的总行数，如果为0，说明没有数据要导出，直接将任务推进到失败状态
        int totalRows = this.baseMapper.selectTotalRows();
        if (totalRows == 0) {
            ExcelTaskEntity excelTask = ExcelTaskEntity.builder()
                    .taskId(taskId)
                    .exceptionType("数据为空")
                    .errorMsg("对应导出任务没有数据可导出！")
                    .updateTime(new Date())
                    .build();
            excelTaskService.updateById(excelTask);
            return;
        }
        // 总数除以每批数量，并向上取整得到批次数
        int batchRows = 2000;
        int batchNum = totalRows / batchRows + (totalRows % batchRows == 0 ? 0 : 1);
        // 总批次数除以并发比例，并向上取整得到并发轮数
        int concurrentRound = batchNum / TaskThreadPool.concurrentRate
                + (batchNum % TaskThreadPool.concurrentRate == 0 ? 0 : 1);;

        log.info("本次报表导出任务-目标数据量：{}条，每批数量：{}，总批次数：{}，并发总轮数：{}", totalRows, batchRows, batchNum, concurrentRound);

        // 提前创建excel写入对象（这里可以替换成上传至文件服务器）
        String fileName = "百万级熊猫数据-" + startTime + ".csv";
        try(ExcelWriter excelWriter = EasyExcelFactory.write(fileName, Panda1mExportVO.class)
                .excelType(ExcelTypeEnum.CSV).build()){
            // CSV文件这行其实可以不需要，设置了也不会生效
            WriteSheet writeSheet = EasyExcelFactory.writerSheet(0, "百万熊猫数据").build();
            // 根据计算出的并发轮数开始并发读取表内数据处理
            AtomicInteger cursor = new AtomicInteger(0);
            ThreadPoolTaskExecutor concurrentPool = TaskThreadPool.getConcurrentThreadPool();
            for (int i = 1; i <= concurrentRound; i++) {
                CountDownLatch countDownLatch = new CountDownLatch(TaskThreadPool.concurrentRate);
                final CopyOnWriteArrayList<Panda1mExportVO> data = new CopyOnWriteArrayList<>();
                for (int j = 0; j < TaskThreadPool.concurrentRate; j++) {
                    final int startId = cursor.get() * batchRows + 1;
                    // log.info("当前批次：{}，起始ID：{}，线程池可用线程数：{}", cursor.get(), startId, concurrentPool.getActiveCount());
                    concurrentPool.submit(()->{
                        List<Panda1mExportVO> pandas = baseMapper.selectPandaPage((long) startId, batchRows);
                        if (null != pandas && !pandas.isEmpty()) {
                            data.addAll(pandas);
                        }
                        countDownLatch.countDown();
                    });
                    cursor.incrementAndGet();
                    try {
                        countDownLatch.await();
                    } catch (InterruptedException e) {
                        ExcelTaskEntity excelTask = ExcelTaskEntity.builder()
                                .taskId(taskId)
                                .handleStatus(TaskHandleStatus.FAILED.getCode())
                                .exceptionType("导出等待中断")
                                .errorMsg(e.getMessage())
                                .updateTime(new Date())
                                .build();
                        excelTaskService.updateById(excelTask);
                        return;
                    }
                    excelWriter.write(data, writeSheet);
                    // 手动清理每一轮的集合数据，用于辅助GC
                    data.clear();
                }
            }
            log.info("处理报表导出任务结束，编号：{}，导出耗时（ms）：{}", taskId, System.currentTimeMillis() - startTime);
            // 完成写入后，主动关闭资源
            excelWriter.finish();

            // 如果执行到最后，说明excel导出成功，将状态推进到导出成功
            ExcelTaskEntity excelTask = ExcelTaskEntity.builder()
                    .taskId(taskId)
                    .handleStatus(TaskHandleStatus.SUCCEED.getCode())
                    .excelUrl(fileName)
                    .updateTime(new Date())
                    .build();
            excelTaskService.updateById(excelTask);
        } catch (Exception e){
            throw new BusinessException("百万数据导出失败: "+e.getMessage());
        }
    }

    @Override
    public Long export1mPandaExcelV2() {
        // 先插入一条报表任务记录
        ExcelTaskEntity excelTask = ExcelTaskEntity.builder()
                .taskType(ExcelTaskType.EXPORT.getCode())
                .handleStatus(TaskHandleStatus.WAIT_HANDLE.getCode())
                .createTime(new Date())
                .build();
        excelTaskService.save(excelTask);
        Long taskId = excelTask.getTaskId();
        // 将报表导出任务提交给异步线程池
        ThreadPoolTaskExecutor asyncPool = TaskThreadPool.getAsyncThreadPool();
        // 必须用try包裹，因为线程池已满时任务被拒绝会抛出异常
        try {
            asyncPool.submit(() -> handleExportTask2(taskId));
        } catch (Exception e) {
            // 记录等待恢复的状态
            log.error("递交异步导出任务被拒，线程池任务已满，任务ID：{}, 错误信息：{}", taskId, e.getMessage());
            ExcelTaskEntity excelTask1 = ExcelTaskEntity.builder()
                    .taskId(taskId)
                    .handleStatus(TaskHandleStatus.WAIT_TO_RESTORE.getCode())
                    .exceptionType("异步线程池任务已满")
                    .errorMsg("等待重新载入线程池被调度！")
                    .updateTime(new Date())
                    .build();
            excelTaskService.updateById(excelTask1);
            // throw new RuntimeException(e);
        }
        return taskId;
    }
}
